﻿using System;
using System.Collections.Generic;
using System.Text;
using AudioPlayer.Plugin.Storage;
using System.Data.SqlServerCe;
using System.IO;
using AudioPlayer.Plugin;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data.SqlTypes;

namespace AudioPlayer.Storage
{
	/// <summary>
	/// Storage implementation
	/// </summary>
	public class SqlCeStorage : IStorage,IPluginHost {
		#region Fields
		private IPlaylist _collection;
		private IList<IPlaylist> _playlists;
		private IHost _host;

		private bool _ready = false;
		#endregion
		#region Private Voids
		private void Init() {
			if (_ready == false) {
				_collection = _host.CreatePlaylist();
				_collection.Primary = true;
				_collection.Name = "Music";
				_collection.Loaded = true;
				_playlists = new List<IPlaylist>();
				_ready = true;
			}
		}
		/// <summary>
		/// Creates the database.
		/// </summary>
		private void CreateDatabase() {

		}

		/// <summary>
		/// Creates the GUID.
		/// </summary>
		/// <param name="song">The song.</param>
		private void CreateGuid(ISong song) {
			SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Items (Item) VALUES ( @p1 )", SqlCeData.Connection);
			cmd.Parameters.Add("@p1", song.Guid);
			int rows = cmd.ExecuteNonQuery();
		}
		/// <summary>
		/// Creates the song.
		/// </summary>
		/// <param name="song">The song.</param>
		private void CreateInfo(ISong song) {
			StringBuilder cmdBuilder = new StringBuilder(1024);
			cmdBuilder.Append("INSERT INTO Songs (Item, Artist, Album, Title, Genre, Comment,Length,Year,Track,Disc) VALUES (@p1,");
			cmdBuilder.Append("'" + song.Artist + "',");
			cmdBuilder.Append("'" + song.Album + "',");
			cmdBuilder.Append("'" + song.Title.Replace("'", "") + "',");
			cmdBuilder.Append("'" + song.Genre + "',");
			cmdBuilder.Append("'" + song.Comment + "',");
			cmdBuilder.Append("'" + song.Length + "',");
			cmdBuilder.Append("'" + song.Year + "',");
			cmdBuilder.Append("'" + song.Track + "',");
			cmdBuilder.Append("'" + song.Disc + "')");
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
			cmd.Parameters.Add("@p1", song.Guid);
			int done = cmd.ExecuteNonQuery();
		}
		/// <summary>
		/// Creates the path.
		/// </summary>
		/// <param name="song">The song.</param>
		private void CreatePath(ISong song) {
			StringBuilder cmdBuilder = new StringBuilder(1024);
			cmdBuilder.Append("INSERT INTO Locations (Item,Path) VALUES (@p1,");
			cmdBuilder.Append("'" + song.Filename.Replace("'", "") + "')");
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
			cmd.Parameters.Add("@p1", song.Guid);
			int done = cmd.ExecuteNonQuery();
		}
		/// <summary>
		/// Creates the counter.
		/// </summary>
		/// <param name="song">The song.</param>
		private void CreateCounter(ISong song) {
			StringBuilder cmdBuilder = new StringBuilder(512);
			cmdBuilder.Append("INSERT INTO Playcounter (Item,PlayUser,PlayAuto,PlaySkip) Values (@p1,");
			cmdBuilder.Append(song.PlayUser + ",");
			cmdBuilder.Append(song.PlayAuto + ",");
			cmdBuilder.Append(song.PlaySkip + ")");
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
			cmd.Parameters.Add("@p1", song.Guid);
			cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// Creates the song.
		/// </summary>
		/// <param name="s">The s.</param>
		/// <param name="pl">The pl.</param>
		private void CreateSong(ISong s,IPlaylist pl) {
			if (!this.Exists(s)) {
				this.CreateGuid(s);
				this.CreateInfo(s);
				this.CreatePath(s);
				this.CreateCounter(s);
			}
			//Update?
			SqlCeCommand cmd = new SqlCeCommand("INSERT INTO PlaylistMap (Playlist,Song) Values (@p1,@p2)", SqlCeData.Connection);
			cmd.Parameters.Add("@p1",pl.Guid);
			cmd.Parameters.Add("@p2",s.Guid);
			cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// Creates the playlist.
		/// </summary>
		/// <param name="pl">The pl.</param>
		private void CreatePlaylist(IPlaylist pl) {
			if (pl.Guid != null) {
				StringBuilder cmdBuilder = new StringBuilder(512);
				cmdBuilder.Append("INSERT INTO Playlist (Playlist,Name,Description) VALUES (@p1,'");
				pl.Guid = Guid.NewGuid();
				cmdBuilder.Append(pl.Name + "','");
				cmdBuilder.Append(pl.Description +"')");
				SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
				cmd.Parameters.Add("@p1",pl.Guid);
				cmd.ExecuteNonQuery();
			} else {
				this.Update(pl);
			}
		}
		/// <summary>
		/// Determines if song exists.
		/// </summary>
		/// <param name="s">The s.</param>
		/// <returns></returns>
		private bool Exists(ISong s) {
			if (s.Guid != null) {
				//create command
				SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Items WHERE (Item = @p1)", SqlCeData.Connection);
				cmd.Parameters.Add("@p1",s.Guid);
				//Execute
				SqlCeDataReader reader = cmd.ExecuteReader();
				while (reader.Read()) {
					return true;
				}
				return false;
			} else {
				//Create command text
				StringBuilder cmdBuilder = new StringBuilder();
				cmdBuilder.Append("SELECT * FROM Locations WHERE (Path = '");
				cmdBuilder.Append(s.Filename.Replace("'", ""));
				cmdBuilder.Append("')");
				//create commmand
				SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
				//Execute it
				SqlCeDataReader reader = cmd.ExecuteReader();
				while (reader.Read()) {
					//If reader can read
					//then it means it has found rows.
					return true;
				}
				return false;
			}
		}

		private void UpdateSongPr(ISong song) {
			this.UpdateSongInfo(song);
		}

		private void UpdateSongInfo(ISong song) {
			StringBuilder cmdBuilder = new StringBuilder(1024);
			cmdBuilder.Append("UPDATE Songs SET (");
			cmdBuilder.Append("Artist = '"+ song.Artist +"',");
			cmdBuilder.Append("Album = '" +song.Album +"',");
			cmdBuilder.Append("Title = '" + song.Title+"',");
			cmdBuilder.Append("Genre = '" + song.Genre +"',");
			cmdBuilder.Append("Comment = '" + song.Comment+"',");
			cmdBuilder.Append("Length = " + song.Length +",");
			cmdBuilder.Append("Year = " + song.Year +",");
			cmdBuilder.Append("Track = " + song.Track +",");
			cmdBuilder.Append("Disc = " + song.Disc +")");
			cmdBuilder.Append("WHERE (Songs.Item = '" + song.Guid + "')'");
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
			cmd.ExecuteNonQuery();
		}
		private void UpdateSongCounter(ISong song) {

		}
		private void UpdateSongLocation(ISong song) {

		}


		/// <summary>
		/// Reads the songs.
		/// </summary>
		private void ReadSongs() {
			string cmdBuild = @"SELECT Items.Item, Songs.Artist, Songs.Album, Songs.Title, 
			Songs.Genre, Songs.Comment,Songs.Length,Songs.Year,Songs.Track,Songs.Disc,Locations.Path,
			PlayCounter.PlayUser,PlayCounter.PlayAuto,PlayCounter.PlaySkip FROM Items INNER JOIN Locations ON Items.Item = Locations.Item INNER JOIN Songs ON Items.Item = Songs.Item INNER JOIN Playcounter ON Items.Item = Playcounter.Item";
			SqlCeCommand cmd = new SqlCeCommand(cmdBuild, SqlCeData.Connection);
			try {
				SqlCeDataReader reader = cmd.ExecuteReader();
				while (reader.Read()) {
					ISong song = _host.CreateSong();
					song.Guid = reader.GetGuid(0);
					song.Artist = reader.GetString(1);
					song.Album = reader.GetString(2);
					song.Title = reader.GetString(3);
					song.Genre = reader.GetString(4);
					song.Comment = reader.GetString(5);
					song.Length = reader.GetInt32(6);
					song.Year = reader.GetInt32(7);
					song.Track = reader.GetInt32(8);
					song.Disc = reader.GetInt32(9);
					song.Filename = reader.GetString(10);
					song.PlayUser = reader.GetInt32(11);
					song.PlayAuto = reader.GetInt32(12);
					song.PlaySkip = reader.GetInt32(13);
					this.Library.Add(song);
				}
			} catch { }
		}

		private void ReadPlaylist() {
			
			string cmdBuilder = "SELECT Playlist,Name,Description FROM Playlist";
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder, SqlCeData.Connection);

			SqlCeDataReader reader = cmd.ExecuteReader();
			while (reader.Read()) {
				IPlaylist pl = _host.CreatePlaylist();
				pl.Guid = reader.GetGuid(0);
				pl.Name = reader.GetString(1);
				pl.Description = (!reader.IsDBNull(2) ? reader.GetString(2) : null);
				pl.Primary = false;
				pl.Loaded = false;
				this.Playlists.Add(pl);
			}
		}
		private void ReadPlaylist(IPlaylist pl) {
			if (pl != null && pl.Guid != null && pl.Guid != Guid.Empty) {
				SqlCeCommand cmd = new SqlCeCommand("SELECT Song FROM PlaylistMap WHERE (Playlist = @p1)", SqlCeData.Connection);
				cmd.Parameters.Add("@p1",pl.Guid);
				SqlCeDataReader reader = cmd.ExecuteReader();
				while (reader.Read()) {
					Guid guid = reader.GetGuid(0);
					if (guid != null) {
						ISong song = this.FindSong(guid);
						if (song != null) {
							pl.Add(song);
						}
					}
				}
			}
			pl.Loaded = true;
		}

		private void UpdatePlaylistInfo(IPlaylist playlist) {
			StringBuilder cmdBuilder = new StringBuilder();
			cmdBuilder.Append("UPDATE Playlist SET ");
			cmdBuilder.Append("Name = '" + playlist.Name + "',");
			cmdBuilder.Append("Description = '" + playlist.Description + "'");
			cmdBuilder.Append(" WHERE (Playlist = '" + playlist.Guid + "')");
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);

			cmd.ExecuteNonQuery();
		}

		private ISong FindSong(Guid guid) {
			for (int i = 0; i < this.Library.Count; i++) {
				if (this.Library[i].Guid == guid) {
					return this.Library[i];
				}
			}
			return null;
		}

		public void Delete(IPlaylist playlist) {
			StringBuilder cmdBuilder = new StringBuilder();
			cmdBuilder.Append("DELETE Playlist WHERE (Playlist = '"+playlist.Guid+"')");
			SqlCeCommand cmd = new SqlCeCommand(cmdBuilder.ToString(), SqlCeData.Connection);
			cmd.ExecuteNonQuery();
		}
		#endregion
		#region Constructor
		public SqlCeStorage() {
			
		}
		#endregion
		#region Public Voids
		/// <summary>
		/// Opens connection, if needed
		/// </summary>
		public void Open() {
			this.Init();
		}

		/// <summary>
		/// Closes the connection.
		/// </summary>
		public void Close() {
			
		}

		/// <summary>
		/// Reads all songs, and adds to main playlist
		/// </summary>
		public void Read() {
			this.Init();
			this.ReadSongs();
			this.ReadPlaylist();
			this.RaiseUpdate();
		}

		public void Load(IPlaylist pl) {
			this.Init();
			this.ReadPlaylist(pl);
		}
		/// <summary>
		/// Puts the songs. to the playlist
		/// </summary>
		/// <param name="playlist">Playlist</param>
		/// <param name="songs">Songs list</param>
		public void Put(IPlaylist playlist,params ISong[] songs) {
			this.Init();
			if (!playlist.Loaded) {
				this.Load(playlist);
			}
			foreach (ISong s in songs) {
				this.CreateSong(s, playlist);
				playlist.Add(s);
			}
		}

		/// <summary>
		/// Puts the playlist.
		/// </summary>
		/// <param name="list">The list.</param>
		public void Put(IPlaylist playlist){
			if (playlist.Guid != null && playlist.Guid != Guid.Empty) {
				this.CreatePlaylist(playlist);
				_playlists.Add(playlist);
			}
		}

		/// <summary>
		/// Updates the song.
		/// </summary>
		/// <param name="song">The song.</param>
		public void Update(ISong song) {
			this.UpdateSongPr(song);
		}
		/// <summary>
		/// Updates the playlist.
		/// </summary>
		/// <param name="playlist">The playlist.</param>
		public void Update(IPlaylist playlist) {
			this.UpdatePlaylistInfo(playlist);
		}
		#endregion
		#region Protected
		protected void RaiseUpdate() {
			if (this.OnUpdate != null) {
				this.OnUpdate(this, EventArgs.Empty);
			}
		}
		#endregion
		#region Property
		/// <summary>
		/// Library playlist
		/// </summary>
		/// <value></value>
		public IPlaylist Library {
			get { return _collection; }
		}

		/// <summary>
		/// Playlists
		/// </summary>
		/// <value></value>
		public IList<IPlaylist> Playlists {
			get { return _playlists; }
		}

		/// <summary>
		/// Gets a value indicating whether this <see cref="IStorage"/> is valid.
		/// </summary>
		/// <value><c>true</c> if valid; otherwise, <c>false</c>.</value>
		public bool Valid {
			get {
				return SqlCeData.Ready;
			}
		}

		
		#endregion
		#region Events
		/// <summary>
		/// Occurs when storage fills main playlist
		/// </summary>
		public event EventHandler OnUpdate;

		#endregion

		#region IDisposable Members
		/// <summary>
		/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
		/// </summary>
		public void Dispose() {
			SqlCeData.Close();
		}

		#endregion
		#region IPluginHost Members

		/// <summary>
		/// Gets or sets the host.
		/// After plugin loaded, the host will be set automaticaly.
		/// </summary>
		/// <value>The Host.</value>
		public IHost Host {
			get {
				return _host;
			}
			set {
				_host = value;
				SqlCeData.Open();
				this.Init();
			}
		}

		#endregion
	}
	public class SqlCeSettings : ISettings {
		#region Fields

		#endregion
		#region Private Voids
		/// <summary>
		/// Ensures everything is ok.
		/// </summary>
		private void Init() {
			if (!SqlCeData.Ready) {
				SqlCeData.Open();
			}
		}

		private bool Exists(Guid owner, string name) {
			string cmbText = "SELECT * FROM Settings WHERE (Guid = @p1 AND Name = '"+name+"')";
			try {
				bool retValue = false;
				SqlCeCommand cmd = new SqlCeCommand(cmbText, SqlCeData.Connection);
				cmd.Parameters.Add("@p1", owner);
				SqlCeDataReader reader = cmd.ExecuteReader();
				while (reader.Read()) {
					retValue = true;
					break;
				}
				reader.Dispose();
				return retValue;
			} catch { return false; }
		}
		#endregion
		#region Public Voids
		/// <summary>
		/// Adds the specified owner.
		/// </summary>
		/// <typeparam name="TField">The type of the field.</typeparam>
		/// <param name="owner">The owner.</param>
		/// <param name="name">The name.</param>
		/// <param name="value">The value.</param>
		public void Set<TField>(Guid owner, string name, TField value) {
			this.Init();
			//Base stuff
			BinaryFormatter bf = new BinaryFormatter();
			MemoryStream memStream = new MemoryStream();
			try {
				bool egz = this.Exists(owner, name);
				//Try to serialize
				bf.Serialize(memStream, value);
				//Get the bytes
				byte[] buffer = memStream.ToArray();
				String cmdText = null;
				if (!Exists(owner, name)) {
					cmdText = "INSERT INTO Settings (Guid, Name, Data) VALUES (@p1,N'" + name + "',@p2)";
				} else {
					cmdText = @"UPDATE Settings SET Data = @p2 WHERE (Settings.Guid = @p1 AND Settings.Name = '" + name + "')";
				}
				SqlCeCommand cmd = new SqlCeCommand(cmdText,SqlCeData.Connection);
				cmd.Parameters.Add("@p1", owner);
				cmd.Parameters.Add("@p2", buffer);
				int rows = cmd.ExecuteNonQuery();
			} catch { }
		}
		/// <summary>
		/// Removes the specified setting
		/// </summary>
		/// <param name="owner">The Owner.</param>
		/// <param name="name">The Name of setting.</param>
		public void Remove(Guid owner, string name) {
			this.Init();
			try {
				SqlCeCommand cmd = new SqlCeCommand(@"DELETE FROM Settings WHERE (Settings.Name = N'"+name+"',Settings.Guid=@p1)", SqlCeData.Connection);
				cmd.Parameters.Add("@p1", System.Data.SqlDbType.UniqueIdentifier);
				cmd.Parameters[0].Value = owner;
				int rows = cmd.ExecuteNonQuery();
			} catch { }
		}

		/// <summary>
		/// Gets the specified owner.
		/// </summary>
		/// <typeparam name="TField">The type of the field.</typeparam>
		/// <param name="owner">The owner.</param>
		/// <param name="name">The name.</param>
		/// <returns>NULL, or value</returns>
		public TField Get<TField>(Guid owner, string name) {
			return this.Get<TField>(owner, name, default(TField));
		}
		/// <summary>
		/// Gets the specified owner.
		/// </summary>
		/// <typeparam name="TField">The type of the field.</typeparam>
		/// <param name="owner">The owner.</param>
		/// <param name="name">The name.</param>
		/// <param name="def">The default value.</param>
		/// <returns></returns>
		public TField Get<TField>(Guid owner, String name, TField def) {
			this.Init();
			try {
				SqlCeCommand cmd = new SqlCeCommand("SELECT Data FROM Settings WHERE (Name = '" + name + "' AND Guid = @p1)", SqlCeData.Connection);
				cmd.Parameters.Add("@p1", owner);
				SqlCeDataReader reader = cmd.ExecuteReader();

				while (reader.Read()) {
					Byte[] buff = new Byte[8000];
					long l = reader.GetBytes(0, 0, buff, 0, 8000);
					//Deserialize
					BinaryFormatter bf = new BinaryFormatter();
					MemoryStream memStream = new MemoryStream(buff, false);

					Object obj = bf.Deserialize(memStream);
					if (obj != null) {
						TField retValue = (TField)obj;
						return retValue;
					}
				}
			} catch { return def; }
			return def;
		}
		/// <summary>
		/// Gets all names with founded owner.
		/// </summary>
		/// <param name="owner"></param>
		/// <returns>
		/// NULL if GUID doesn't contain any values, or Array.
		/// </returns>
		public String[] Get(Guid owner) {
			this.Init();
			try {
				//Sql
				SqlCeCommand cmd = new SqlCeCommand("SELECT Name FROM Settings WHERE (Guid = @p1)", SqlCeData.Connection);
				cmd.Parameters.Add("@p1", owner);
				//Get reader
				SqlCeDataReader reader = cmd.ExecuteReader();
				//create return list
				List<String> retList = new List<string>();
				while (reader.Read()) {
					retList.Add(reader.GetString(0));
				}
				return retList.ToArray();
			} catch { }
			return null;
		}
		#endregion
		#region IDisposable Members

		public void Dispose() {
			
		}

		#endregion
	}
	public static class SqlCeData {
		#region Fields
		private static SqlCeConnection _connection;
		#endregion
		#region Private Voids
		/// <summary>
		/// Creates new database file.
		/// </summary>
		private static void Create() {

		}
		#endregion
		#region Public Voids
		/// <summary>
		/// Opens connection.
		/// </summary>
		public static void Open() {
			try {
				if (File.Exists("Plugin\\Library.sdf")) {
					_connection = new SqlCeConnection(@"Data Source=Plugin\Library.sdf");
					_connection.Open();
				} else {
					SqlCeData.Create();
					_connection.Open();
				}
			} catch { }
		}
		/// <summary>
		/// Closes connection.
		/// </summary>
		public static void Close() {
			if (SqlCeData.Ready) {
				_connection.Close();
			}
		}
		#endregion
		#region Property
		/// <summary>
		/// Gets or sets the connection.
		/// </summary>
		/// <value>The connection.</value>
		public static SqlCeConnection Connection {
			get {
				return _connection;
			}
			set {
				_connection = value;
			}
		}

		/// <summary>
		/// Gets a value indicating whether connection is ready.
		/// </summary>
		/// <value><c>true</c> if ready; otherwise, <c>false</c>.</value>
		public static bool Ready {
			get {
				if (_connection != null && _connection.State == System.Data.ConnectionState.Open) {
					return true;
				} else {
					return false;
				}
			}
		}
		#endregion
	}
}
